package cn.luderson.daos;

import cn.luderson.users.*;


import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Properties;

public class DAO {
    private static Connection getConn() {
        Connection conn = null;
        try {
            InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties");
            Properties pro = new Properties();
            pro.load(in);
            String driver = pro.getProperty("jdbc.driver");
            String url = pro.getProperty("jdbc.url");
            String username = pro.getProperty("jdbc.user");// 
            String password = pro.getProperty("jdbc.password");// 
            Class.forName(driver); // classLoader  
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static int insert(Student student) {
        Connection conn = getConn();
        int i = 0;
        String sql = "insert into students (Name,Sex,Age) values(?,?,?)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1, student.getName());
            pstmt.setString(2, student.getSex());
            pstmt.setString(3, student.getAge());
            i = pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    public static int update(Student student) {
        Connection conn = getConn();
        int i = 0;
        String sql = "update students set Age='" + student.getAge() + "' where Name='" + student.getName() + "'";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            System.out.println("resutl: " + i);
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    public static Integer getAll() {
        Connection conn = getConn();
        String sql = "select * from students";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            int col = rs.getMetaData().getColumnCount();
            System.out.println("============================");
            while (rs.next()) {
                for (int i = 1; i <= col; i++) {
                    System.out.print(rs.getString(i) + "\t");
                    if ((i == 2) && (rs.getString(i).length() < 8)) {
                        System.out.print("\t");
                    }
                }
                System.out.println("");
            }
            System.out.println("============================");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //
    public static String getLatestCardIdAsString(String user_code) {
        Connection conn = getConn();
        String sql = "select record_id,card_date from yq_health_card_daily_record where user_code = '"+user_code+"'   ORDER BY record_id DESC LIMIT 1 ";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                String ret = rs.getString("record_id");
                // System.out.println("record_id:" + ret);
                // System.out.println("card_date:" + rs.getDate("card_date"));
                return ret;
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  "__NOTHING__";
    }

    // 
    public static Date getCardDate(String record_id) {
        Connection conn = getConn();
        String sql = "select card_date from yq_health_card_daily_record where record_id = '"+record_id+"'";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                Date ret = rs.getDate("card_date");
                return ret;
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  null;
    }

    public static String computeLatestRecordIdAsString(){
        Connection conn = getConn();
        String sql = "SELECT record_id from yq_health_card_daily_record ORDER BY record_id DESC LIMIT 1  ";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                String ret = rs.getString("record_id");
                // System.out.println("old latest record_id:" + ret);
                long temp = Long.parseLong(ret.substring(0,13)) +1 ;
                ret = "" + String.valueOf(temp) + "" + (int)((Math.random()*9+1)*100000);
                // System.out.println("new latest record_id:" + ret);
                return ret;
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "";
    }

    public static void addEmpryRecord(String record_id) {
        Connection conn = getConn();
        PreparedStatement pstmt;
        String sql = "INSERT INTO yq_health_card_daily_record (record_id,card_date,user_code,type,`name`) "
                    +"  VALUES ( '"+record_id+"', CURRENT_DATE(), 'user_code','type','name' ) ";
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 复制功能 指定新id
    public static boolean copyRecord(String oldRecordId, String newRecordId) {
        Connection conn = getConn();
        PreparedStatement pstmt;
        String sql = ""
            +"UPDATE yq_health_card_daily_record as a "
            +"LEFT JOIN yq_health_card_daily_record as b  ON b.record_id = '"+ oldRecordId +"' "
            +"SET a.	card_date	=	CURRENT_DATE()	, "
            +"    a.	user_code	=	b.	user_code	, "
            +"    a.	type	=	b.	type	, "
            +"    a.	name	=	b.	name	, "
            +"    a.	sex	=	b.	sex	, "
            +"    a.	dept_name	=	b.	dept_name	,"
            +"    a.	province	=	b.	province	,"
            +"    a.	city	=	b.	city	,"
            +"    a.	area	=	b.	area	,"
            +"    a.	address	=	b.	address	,"
            +"    a.	health	=	b.	health	,"
            +"    a.	epidemic_situation	=	b.	epidemic_situation	,"
            +"    a.	go_out	=	b.	go_out	,"
            +"    a.	contact_hubei_patient	=	b.	contact_hubei_patient	,"
            +"    a.	create_time	=	b.	create_time	,"
            +"    a.	update_time	=	b.	update_time	,"
            +"    a.	del_flag	=	b.	del_flag	,"
            +"    a.	user_id	=	b.	user_id	,"
            +"    a.	dept_id	=	b.	dept_id	,"
            +"    a.	addr_code	=	b.	addr_code	,"
            +"    a.	campus_living	=	b.	campus_living	,"
            +"    a.	campus_building	=	b.	campus_building	,"
            +"    a.	campus_return_work	=	b.	campus_return_work	,"
            +"    a.	stu_area	=	b.	stu_area	,"
            +"    a.	stu_building	=	b.	stu_building	,"
            +"    a.	stu_dormitory_no	=	b.	stu_dormitory_no	"
            +" WHERE a.record_id = '"+newRecordId+"' "
        ;
        
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.executeUpdate();
            pstmt.close();
            conn.close();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public static void updateHealthCardToday(String user_code) {
        Connection conn = getConn();
        PreparedStatement pstmt;
        String sql = "UPDATE yq_health_card SET card_date=CURRENT_DATE() WHERE user_code = '"+user_code+"'";
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static int delete(String name) {
        Connection conn = getConn();
        int i = 0;
        String sql = "delete from students where Name='" + name + "'";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            System.out.println("resutl: " + i);
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
}
